﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;

namespace Db
{
    public class SaleDb : AbstractDb
    {
        EventDb eventDb = new EventDb();
        MovieDb movieDb = new MovieDb();
        CinemaDb cinemaDb = new CinemaDb();
        public Sale GetSaleByID(int id)
        {
            Sale sale = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Sale WHERE sale_ID = " + id);
            if (q.HasRows && q.Read())
            {
                int uid = q.GetInt32(q.GetOrdinal("sale_ID"));
                byte paid = q.GetByte(q.GetOrdinal("sale_paid"));
                int orderNr = q.GetInt32(q.GetOrdinal("sale_orderNr"));
                Event even = eventDb.GetEventByID(q.GetInt32(q.GetOrdinal("sale_eventID")));
                int quantity = q.GetInt32(q.GetOrdinal("sale_quantity"));
                double discount = q.GetDouble(q.GetOrdinal("sale_discount"));
                byte discountProcent = q.GetByte(q.GetOrdinal("sale_discountProcent"));

                sale = new Sale(uid, orderNr, paid, even, quantity, discount, discountProcent);
            }
            return sale;
        }

        public List<Sale> GetAllSales()
        {
            List<Sale> sales = new List<Sale>();
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Sale");
            if (q.HasRows)
            {
                while (q.Read())
                {
                    int uid = q.GetInt32(q.GetOrdinal("sale_ID"));
                    byte paid = q.GetByte(q.GetOrdinal("sale_paid"));
                    int orderNr = q.GetInt32(q.GetOrdinal("sale_orderNr"));
                    Event even = eventDb.GetEventByID(q.GetInt32(q.GetOrdinal("sale_eventID")));
                    int quantity = q.GetInt32(q.GetOrdinal("sale_quantity"));
                    double discount = q.GetDouble(q.GetOrdinal("sale_discount"));
                    byte discountProcent = q.GetByte(q.GetOrdinal("sale_discountProcent"));

                    Sale sale = new Sale(uid, orderNr, paid, even, quantity, discount, discountProcent);
                    sales.Add(sale);
                }
            }
            return sales;
        }

        public void InsertSale(Sale sale)
        {
            Statement stmt = new Statement("INSERT INTO Bif_Sale VALUES sale_paid = @paid, sale_orderID = @orderID, sale_eventID = @eventID, sale_quantity = @quantity, sale_total = @total, sale_discount = @discount, sale_discountProcent = @procent");

            stmt.AddParameter("@paid", sale.Paid, "tinyint");
            stmt.AddParameter("@orderNr", sale.OrderNr, "int");
            stmt.AddParameter("@eventID", sale.Event.Id, "int");
            stmt.AddParameter("@quantity", sale.Quantity, "int");
            stmt.AddParameter("@total", sale.Total, "float");
            stmt.AddParameter("discount", sale.Discount, "float");
            stmt.AddParameter("@procent", sale.DiscountProcent, "tinyint");

            this.db.QueryInsert(stmt);
        }

        public List<Sale> GetAllNotPaidSales()
        {
            List<Sale> sales = new List<Sale>();
            Statement stmt = new Statement("SELECT * FROM Bif_Sale WHERE sale_paid = 0");
            SqlDataReader q = this.db.Query(stmt);
            if (q.HasRows)
            {
                while (q.Read())
                {
                    int uid = q.GetInt32(q.GetOrdinal("sale_ID"));
                    byte paid = q.GetByte(q.GetOrdinal("sale_paid"));
                    int orderNr = q.GetInt32(q.GetOrdinal("sale_orderNr"));
                    Event even = eventDb.GetEventByID(q.GetInt32(q.GetOrdinal("sale_eventID")));
                    int quantity = q.GetInt32(q.GetOrdinal("sale_quantity"));
                    double discount = q.GetDouble(q.GetOrdinal("sale_discount"));
                    byte discountProcent = q.GetByte(q.GetOrdinal("sale_discountProcent"));

                    Sale sale = new Sale(uid, orderNr, paid, even, quantity, discount, discountProcent);
                    sales.Add(sale);
                }
            }
            return sales;
        }

        public List<Sale> GetAllPaidSales()
        {
            List<Sale> sales = new List<Sale>();
            Statement stmt = new Statement("SELECT * FROM Bif_Sale WHERE sale_paid = 1");
            SqlDataReader q = this.db.Query(stmt);
            if (q.HasRows)
            {
                while (q.Read())
                {
                    int uid = q.GetInt32(q.GetOrdinal("sale_ID"));
                    byte paid = q.GetByte(q.GetOrdinal("sale_paid"));
                    int orderNr = q.GetInt32(q.GetOrdinal("sale_orderNr"));
                    Event even = eventDb.GetEventByID(q.GetInt32(q.GetOrdinal("sale_eventID")));
                    int quantity = q.GetInt32(q.GetOrdinal("sale_quantity"));
                    double discount = q.GetDouble(q.GetOrdinal("sale_discount"));
                    byte discountProcent = q.GetByte(q.GetOrdinal("sale_discountProcent"));

                    Sale sale = new Sale(uid, orderNr, paid, even, quantity, discount, discountProcent);
                    sales.Add(sale);
                }
            }
            return sales;
        }

        public void DeleteSaleByID(int id)
        {
            Statement stmt = new Statement("DELETE FROM Bif_Sale WHERE sale_ID = @id");
            stmt.AddParameter("@id", id, "int");
            this.db.QueryDelete(stmt);
        }

        public void PaySale(Sale sale)
        {
            Statement stmt = new Statement("UPDATE Bif_Sale SET sale_paid = 1, sale_orderNr = @orderNr WHERE sale_id = @id");
            stmt.AddParameter("@id", sale.Id, "int");
            stmt.AddParameter("@orderNr", Convert.ToInt32(this.db.GetMax("Bif_Sale", "sale_orderNr")), "int");
            this.db.QueryUpdate(stmt);
        }
    }
}
